Copyright: Copyright © 1992-1995 Blue Lagoon Software, Inc.
Citation: Copyright © 1992-1995 Blue Lagoon Software, Inc.


Contents
ODBC Inspector for Windows

Version 2.0

Copyright © 1992-1995 by Blue Lagoon Software Inc.

Blue Lagoon Software

6047 Tampa Ave. Suite 209

Tarzana, CA  91356 USA

Tel.	(818) 345-2200

Fax	(818) 345-8905

BBS	(818) 343-8433

CompuServe 70152,1601

Internet blagoon@netcom.com

Contents

Disclaimer

Introduction

Menu commands

Options dialog box

ODBC Inspector's traced data

Network tracing

Viewing Multi-Workstation Log Files

ODBC operations

JET Inspector Integration


Disclaimer

ODBC Inspector for Windows, Copyright © 1992-1995 Blue Lagoon Software Inc., is provided as-is without warranty of any kind.  Blue Lagoon Software Inc. shall not be liable for any loss of profits, loss of use, interruption of business, nor indirect, special, incidental or consequential damages of any kind from the use of this product.


Introduction

Thank you for purchasing ODBC Inspector for Windows!

ODBC provides applications under Windows 3.1, whether written in Microsoft Visual Basic, Microsoft Access, Power Builder, C, or Gupta SQLWindows, with a way to communicate with database servers through a central point.  ODBC Inspector for Windows does performance monitoring and tracing at that central point, providing a database-independent means of obtaining performance and trace information.

The version of ODBC Inspector for Windows you have purchased is to be used under Windows 3.1.  The Inspector allows you to trace all ODBC API calls simultaneously.

To use this product, we recommend that your computer have a 386 or higher processor and at least 8MB of RAM.

To make the most out of ODBC Inspector for Windows, you should have a copy of The Microsoft ODBC SDK API handy.  This valuable reference lists all of the ODBC API along with the parameters passed and return values.  This information will be needed when examining ODBC Inspector’s traced data.


Menu commands

ODBC Inspector for Windows has five menus: Inspector, Marker, Find, Window, and Help.  Accelerator keystrokes are provided to access many of the menu commands; these are listed in the table below:

Keystroke	Menu Command

Ctrl+I	Inspector On/Off

Ctrl+O	Options dialog

Ctrl+F	Find text

Ctrl+R	Find First

Ctrl+P	Find Previous

Ctrl+N	Find Next

Ctrl+L	Find Last

Ctrl+W	Clear Window

Inspector menu commands

Inspector On/Off

Toggles trace mode.  When ODBC Inspector is tracing, the caption bar of the application will be set to "<< ODBC Inspector >>".  When ODBC Inspector is not tracing, the caption bar of the application will be set to "ODBC Inspector".

Options

Brings up the ODBC Inspector Options dialog box.  It is within this dialog box that tracing options are setup.

Exit

Exits ODBC Inspector.

Marker! menu command

Brings up the Marker dialog box.  The Marker dialog box is designed to allow user entry of a string of up to 255 characters which will be inserted into the trace log.

Find menu commands

Find Text...

Brings up the Find dialog box.  This is a "floating" dialog box used to search for text within the Inspector's output window.

In the Find what box, enter the text you wish to search for.  Check the Match Case box by clicking on it if you want to make the search case-sensitive.  Then, click on the Find First button to find the first occurrence of the text; you can also search for the next, previous or last occurrence by clicking on the appropriate button.

The Find dialog box remembers the last five text strings you have searched for in the current ODBC Inspector session; you can select one of these by clicking on the down-arrow next to the Find what box.

You can switch back to the Inspector window by clicking on it, and the Find window will remain open.

Find First

Find Previous

Find Next

Find Last

These menu commands find the appropriate occurrence of the last text string specified in the Find dialog box.

Window menu commands

Clear Window

Clears the ODBC Inspector window.  Any output in the ODBC Inspector window will be cleared.

Help menu commands

Contents

Brings up the contents portion of the help file.

About

Displays the application "about" box.


ODBC Inspector's traced data

It is possible to have ODBC Inspector trace data into ODBC Inspector’s application window and/or a trace log file in ASCII format.  The application window can hold approximately 3000 lines of Extended Mode output.  The capacity of the log file is limited only by available disk space.

ODBC Inspector has three output modes: Extended, Query and Script.  These are described in the following sections.

Extended Mode

When an ODBC operation occurs, ODBC Inspector reports the execution time of the operation in seconds:milliseconds, the application that executed the operation, the name of the operation along with the parameters and return value.  This information is displayed in the application window and/or saved to the trace log file in the following format:

000:016 MSACCESS: SQLExecDirect(1257177088, “select * from foo”, 17), RETVAL=SQL_SUCCESS

The output of each operation is placed on a single line by itself.  Each output line contains the following information:

•	Timestamp when the API call began (if timestamps are enabled)

•	Workstation name (if workstation names are enabled)

•	Execution time of the call, in seconds:milliseconds

•	Application name

•	ODBC function name and parameters

For example, it took 16 milliseconds to execute the above ODBC operation.  This performance information is especially useful for commands such as SQLExecDirect and SQLExecute because they send SQL queries to database servers for execution.  By measuring their performance, it is possible to determine how long queries and stored procedures take to execute.

According to the ODBC SDK API documentation, SQLExecDirect has three parameters: a handle to the database connection, a long pointer to the SQL command to execute, and the number of characters in the SQL command.  The return value can be any of the values documented in the SDK.  As we can see, the SQLExecDirect call was made by Microsoft Access, the database handle is 1257177088, and the command being executed is “select * from foo”.  The return value is SQL_SUCCESS.

Options exist within ODBC Inspector to allow the time stamp and workstation name to be included in each trace output line.  The time stamp is very useful because it allows you to see what time a particular ODBC operation took place.  The workstation name is also very useful when the trace log file is located on a network drive and is shared by many different users.

Query Mode

When in Query Mode, the ODBC Inspector only outputs trace data for server connect and disconnect operations, and query execution operations.  This information is output in the following format:

-----------------------------------------------------------

MICHAEL 000:643 MSQUERY

<< SERVER CONNECT >>

Connection:

DSN=pubs;UID=sa;;PWD=[Hidden];APP=Microsoft Query;

WSID=PAUL;DATABASE=pubs

*** SQL_SUCCESS_WITH_INFO ***

-----------------------------------------------------------

MICHAEL 000:007 MSQUERY

SELECT authors.au_fname, discounts.highqty, publishers.city  FROM pubs.dbo.authors authors, pubs.dbo.discounts discounts, pubs.dbo.publishers publishers, pubs.dbo.roysched roysched

*** SQL_STILL_EXECUTING ***

-----------------------------------------------------------

Script Mode

When the ODBC Inspector is configured for Script mode, it outputs raw queries as passed to the ODBC SQLPrepare and SQLExecDirect functions.  After each raw query, the Inspector adds the delimiter specified in the Options dialog box; for example, it could be configured to add the word "go" as a delimiter after each query.

Note that the Delimiter box only appears in the Options dialog when you have selected Script mode; it does not apply to other output modes.

Retrieved Data Output Formats

Several ODBC functions return variable-format data, which the ODBC Inspector displays in its Extended Mode output.  Where possible, the Inspector identifies the correct data type and displays the data in that format.  In cases where the format cannot be identified, the Inspector will show the data in hexadecimal format (i.e. a hex dump).

Below is a listing of the data types recognized by the Inspector, along with the output format of each.

Data Type	Output Format

SQL_C_CHAR	Text string

SQL_C_LONG	Numeric integer

SQL_C_SHORT	Numeric integer

SQL_C_FLOAT	Numeric decimal

SQL_C_DOUBLE	Numeric decimal

SQL_C_DEFAULT	Default data type for column (if known), or else hex dump

SQL_C_DATE	MM/DD/YY

SQL_C_TIME	HH:MM:SS

SQL_C_TIMESTAMP	MM/DD/YY HH:MM:SS

SQL_C_BINARY	Hex dump

SQL_C_BIT	True/False

SQL_C_TINYINT	Numeric integer

SQL_C_SLONG 	Numeric integer

SQL_C_SSHORT	Numeric integer

SQL_C_STINYINT	Numeric integer

SQL_C_ULONG 	Numeric integer

SQL_C_USHORT	Numeric integer

SQL_C_UTINYINT	Numeric integer

SQL_C_BOOKMARK	Numeric integer


Options dialog box

Display mode combo box

The Display mode combo box contains a list of the different trace modes available.  The ODBC Inspector currently supports Extended, Query and Script modes.  These modes are described in the section titled ODBC Inspector's Traced Data.

Programs combo box

The Programs combo box contains a list of all programs currently running under Windows.  To trace a specific applications’ ODBC accesses, first make sure that the application is currently running, then select it from this combo box.  ODBC Inspector will only display ODBC operations made by this application.  The "(All)" option is designed to display ODBC operations made by ALL applications.

Operations list box

The Operations list box is a multi-selection list box.  You can select some or all of the listed operations to be displayed by the Inspector when they occur.  This allows you to filter out the operations you’re not interested in tracing.  Note that this list box is not available in Query or Script display mode; these modes automatically select which operations to monitor in order to produce their output.

Select all push button

Selects ALL operations in the Operations list box.

Deselect all push button

Deselects ALL operations in the Operations list box.

Window check box

This check box should be selected to indicate that you want ODBC Inspector to display what it traces in the application area of the ODBC Inspector’s application window.

Workstation check box

This check box should be selected to indicate that you want ODBC Inspector to include the workstation name on each line of the trace output.  The workstation name is set by entering a 16 character string in the entry field adjacent to this check box.

Time stamp check box

This check box should be selected to indicate that you want ODBC Inspector to include the time stamp on each line of the trace output.

File check box

This check box should be selected to indicate that you want ODBC Inspector to create a log file which will contain all traced data.  As ODBC Inspector traces, it will append its data in ASCII format to a log file whose path name is specified in the adjacent entry field.

Append file check box

This check box should be selected to indicate that you want ODBC Inspector to open the trace log file in append mode.  If this check box is not selected and the file check box is selected, then each time the ODBC Inspector is started, the trace log file will be truncated to zero length.

OK push button

Accepts the changes made.

Cancel push button

Cancels the changes made.


Network Multi-Workstation Tracing

The ODBC Inspector’s log file tracing mechanism uses network file sharing.  This means that by enabling the File and Append file features in the Options dialog box, all traced output will be appended to the trace log file using network file sharing.  Thus, when more than one user has this feature enabled and the same network trace log file is used, then ODBC operations for these users will be written to the same trace log file in the order in which they occur.

Naturally, if several users are sharing a network trace log file, the workstation name feature becomes very useful in identifying which ODBC operations occurred on a particular workstation.  In addition, the time stamp is also very useful to help understand when an operation occurred.

By only tracing ODBC operations that are necessary, better performance will be available for the application that is being traced.  In addition, with several users tracing to the same network trace log file, choosing ODBC operations becomes all the more important since each workstation running the Inspector will use file locking to prevent access to the file while it is writing its trace output.


ODBC operations

The following is a list of commonly used ODBC operations which are traceable in ODBC Inspector's Extended display mode.

SQLBindCol

"Binds," or associates, a data buffer with a column in a result set.  Subsequent SQLFetch calls update the buffer with the data for the specified column in the newly-fetched row.  ODBC Inspector displays the returned data stored in a bound column buffer after every SQLFetch call that affects the buffer.

SQLConnect

Displays connect information about an ODBC connection such as server name, user name, password, data source type, etc.  Note that the ODBC Inspector normally does not show password strings in its output, in the interest of preserving security.  If you need to see the password information, please contact Blue Lagoon Software.

SQLDisconnect

Indicates that an ODBC connection has been closed.

SQLDriverConnect

Alternative to SQLConnect.

SQLExecDirect

Executes a preparable SQL statement.  The SQL query text is passed as a parameter to this operation.  The execution time for this operation indicates the total amount of execution time for that query.

SQLFetch

Retrieves the next row of data from the specified result data set.  After each SQLFetch operation, ODBC Inspector automatically shows any bound column data updated by the SQLFetch call.

SQLExecute

Executes a SQL query which was prepared by SQLPrepare.  The total execution time of this operation plus the total execution time of the prior SQLPrepare operations indicates the total amount of execution time for this query.

SQLGetData

Retrieves the result data from a previous SQLExecDirect or SQLExecute.  This is how the result data is being retrieved by the application being traced.  ODBC Inspector displays the returned data in the format appropriate for the data type.  Note:  this operation may occur frequently, depending on the application being traced.  Thus, tracing this operation may slow the performance of your system, and if network multi-workstation tracing is being performed, it may slow the performance of other systems.

SQLPrepare

Prepares an SQL string for execution.  The SQL query text is passed as a parameter to this operation.  The execution time for this operation only indicates the amount of time to prepare the SQL string for execution.  The actual execution of this query takes place when the next  SQLExecute occurs.


JET Inspector Integration

Microsoft Visual Basic, Microsoft Access, and the Microsoft Access ODBC driver can be used to take advantage of ODBC.  In fact, if you are using one of those development tools, then this section may be of interest to you.

A very rich and powerful database implementation has been made available through these tools.  The Microsoft JET Database Engine, which is at the heart of this database implementation, allows developers to take advantage of nearly any database through ODBC without requiring any special knowledge of ODBC.  This is accomplished through data-aware controls and an object-based data access language which isolate the developer from the specifics of the database interface.

The Microsoft JET database engine performs many tasks on behalf of the developer.  It is through JET that ODBC is called and utilized, thus allowing a VB or Access developer to take advantage of any database that supports ODBC.  Our JET Inspector traces and provides performance monitoring of JET operations.  When these JET operations make use of ODBC services, the ODBC Inspector works in conjunction with the JET Inspector to provide additional information on ODBC performance.

If you have the JET Inspector installed on your system, through the Options dialog box in the JET Inspector, a check box labelled “ODBC Inspector” may be selected, which establishes an object link between the JET and ODBC Inspectors.  This object linking technology (OLT) will launch the ODBC Inspector as a server component  that will provide information to the JET Inspector as JET operations occur which involve ODBC.


Viewing Multi-Workstation Log Files

ODBC Inspector includes a utility, Multi-Workstation Log Viewer, which allows a network administrator to view a shared log file.  This utility can be started by selecting the Launch Log File Viewer item from the Inspector menu, or by double-clicking the Log Viewer's icon in the Program Manager.

The Viewer normally displays the end of the log file, and continually scrolls new data into view as it is written to the log file by any workstations.

Choosing the Tail Off! command from the menu causes the Viewer not to automatically show new data; this lets the user browse through the file without being interrupted by new data.

The Viewer normally brings up the log file as configured in the ODBC Inspector's Options dialog box.  However, the user may open a different file by choosing the Open... command from the File menu.

The Find menu commands bring up a dialog box that lets the user search the log file for a given text string.  As in the Inspector, the user can perform Find First, Find Previous, Find Next and Find Last operations to locate a specific occurrence of the search text.

The Find window remains active until it is closed, to allow repeating of searches.


Find Dialog Box

Selecting the Find text... menu command Brings up the Find dialog box.  This is a "floating" dialog box used to search for text within the Inspector's output window.

In the Find what box, enter the text you wish to search for.  Check the Match Case box by clicking on it if you want to make the search case-sensitive.  Then, click on the Find First button to find the first occurrence of the text; you can also search for the next, previous or last occurrence by clicking on the appropriate button.

The Find dialog box remembers the last five text strings you have searched for in the current SQL Inspector session; you can select one of these by clicking on the down-arrow next to the Find what box.

You can switch back to the Inspector window by clicking on it, and the Find window will remain open.

The Find First, Find Previous, Find Next and Find Last menu commands find the appropriate occurrence of the last text string specified in the Find dialog box.


Marker Dialog Box

This dialog box lets you enter a string of up to 255 characters to be written into the Inspector's window and log file output; this feature is useful for inserting annotations into the traced output.